# build in python3.5.2
# 作者：陈常鸿
# python操作MySQL,把比特币历史成交数据存入数据库,程序并不是很稳定，会与数据库断开
import pymysql as mysql
import requests

trades=lambda url,token,TID:requests.get(url+token+TID).json()
tradeID=8522729

# 链接数据库
db=mysql.connect(host='localhost', port=3306, user='root', passwd='231495877')
# 使用cursor()方法获取操作游标
cursor=db.cursor()
# 数据库已经在SQL命令里创建，python只需要写入与读取即可
# 创建数据表语句

sql = ['use btc', 'show tables', 'desc history']
# 下面是执行语句，提交到数据库，上面的sql字符是传到SQL客户端的命令行
try:
    for cmd in sql:
        cursor.execute(cmd)
        feedback = cursor.fetchall()  # 获取SQL的反馈
        for row in feedback:
            print(row[0])
except Exception as error:
    print('命令执行失败，原因：', error)

db.commit()


while True:
    a = trades('https://data.gateio.io/api2/1/tradeHistory/', 'btc_usdt', '/' + str(tradeID))
    for i in a['data']:
        tradeID=i['tradeID']
        print('TID', tradeID)
        insert = 'insert into history values({0},{1},"{2}",{3},{4})'.format(i['tradeID'],i['total'],i['date'][:10],i['rate'], i['amount'])
        print(insert)
        try:
            cursor.execute(insert)
        except Exception as error:
            print("循环 内部错误：",error)

        db.commit()
    tradeID = str(int(tradeID) + 1)
    db.close()

